﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace ZeusLite.Code
{
  /// <summary>
  /// Sql Connection
  /// </summary>
  public class SqlConn : IDisposable
  {
    /// <summary>
    /// 
    /// </summary>
    private SqlConnection conn = null;

    /// <summary>
    /// 
    /// </summary>
    public SqlConn()
    {

    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="lang"></param>
    /// <returns></returns>
    private string SqlLanguage(string lang)
    {
      try
      {
        switch (lang.ToLower())
        {
          case "hu-hu":
            return "Hungarian";

          case "de-de":
            return "German";
          
          case "en-uk":
            return "British";

          default:
          case "en-us":
            return "English";
        }
      }
      catch
      {
        return "English";
      }
    }

    /// <summary>
    /// Open connection
    /// </summary>
    /// <param name="connectionString"></param>
    public void Open(string connectionString, string lang)
    {
      if (conn == null)
      {
        conn = new SqlConnection(connectionString + ";Current Language=" + SqlLanguage(lang));
        conn.Open();
      }
    }

    /// <summary>
    /// Close connection
    /// </summary>
    public void Close()
    {
      if (conn != null)
      {
        conn.Close();
        conn = null;
      }
    }

    /// <summary>
    /// Execute SQL command
    /// </summary>
    /// <param name="sqltext"></param>
    /// <param name="parameters"></param>
    /// <param name="tran"></param>
    /// <returns></returns>
    public DataTable Execute(string sqltext, Array parameters, bool transaction = false)
    {
      DataTable table = new DataTable();

      using (SqlCommand cmd = new SqlCommand(sqltext, conn))
      {
        // maybe...
        if (transaction == true)
          cmd.Transaction = cmd.Connection.BeginTransaction();

        try
        {
          // Parameters
          cmd.Parameters.AddRange(parameters);

          // Execute command and get result
          using (SqlDataAdapter da = new SqlDataAdapter(cmd))
          {
            da.Fill(table);
          }

          // OK
          if (transaction == true)
            cmd.Transaction.Commit();

          return table;
        }
        catch (Exception ex)
        {
          try
          {
            // FAILED
            if (transaction == true)
              cmd.Transaction.Rollback();
          }
          catch { }

          throw ex;
        }
      }
    }

    /// <summary>
    /// 
    /// </summary>
    public void Dispose()
    {
      Close();
    }
  }
}